insert overwrite table jms_dm.dm_problem_record_keywords_detail_dt partition(dt)
select
     waybill_no                       -- 运单
    ,reply_network_code               -- 回复网点code
    ,reply_network_name               -- 回复网点名称
    ,reply_time                       -- 回复时间
    ,registration_network_name        -- 登记网点code
    ,registration_network_code        -- 登记网点名称
    ,registration_time                -- 登记时间
    ,case when reply_content = '暂放3天。' then '暂放3天' end as message_keywords
    ,dt
from(
    select 
        b.waybill_no
        ,a.reply_network_code           -- 回复网点code
        ,a.reply_network_name           -- 回复网点名称
        ,a.create_time                  as reply_time -- 回复时间
        ,a.reply_content
        ,b.registration_network_name    -- 登记网点
        ,b.registration_network_code    -- 登记网点
        ,b.create_time                  as registration_time  -- 登记时间
        ,a.dt
    from 
        jms_dwd.dwd_problem_service_record_base_dt a
        inner join jms_dwd.dwd_sqs_registration_problem_piece_base_dt b
            on a.problem_piece_id = b.id
    where
        a.dt = '{{ execution_date | cst_ds }}'
        and a.is_enable = 1
        and a.is_delete = 1
        and b.dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
        and b.proble_type_subject_code <> 16   -- 剔除拒收件
        and reply_content = '暂放3天。'
) problem_waybill
left join (
    select
        a.waybill_id
        ,max(if(a.field_name = 'SVC-FRESH', 1, 0)) as fresh_flag
        -- ,b.value
    from
        jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
            -- left join jms_dim.dim_yl_oms_system_dict_base b on a. field_name = b.label and b.is_delete = 0
    where
        dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
    group by 
        a.waybill_id
        ,a.field_name
) order_flag
    on problem_waybill.waybill_no = order_flag.waybill_id 
where 
    nvl(order_flag.fresh_flag, 0) <> 1    -- 剔除生鲜件
distribute by 1
;


